Release 10.1A: OpenEdge Data Management:
Database Administration

Managing fragmentation

Records are allocated to a block or blocks according to algorithms that aim to maximize storage utilization, and minimize fragmentation. When allocating space for a new record, the database engine first attempts to find space in the blocks on the RM (record management) chain. The RM chain contains partially filled blocks. If a block is not found on the RM chain, the record will be inserted into a block from the free chain. The free chain contains empty blocks. Changing create and toss limits determines where new records are inserted and the amount of unused space a block must contain when on the RM chain. Unused space is intentionally left within a block to allow for future expansion of the record. The goal is that the free space is sufficient to handle anticipated growth and that there will not be a need to split the record across blocks.

The limits delineating the free space thresholds are defined as:

Create Limit — The minimum amount of free space in bytes required in a block after a new record is inserted. The create limit must be greater than 32, and less than the block size minus 128. For a database with a 1K block size, the default create limit is 75. For all other block sizes, the default create limit is 150.
Toss Limit — The minimum amount of free space in bytes required in a block for the block to remain on the RM chain. Once a block contains fewer empty bytes than the toss limit, it is removed from the RM chain, and any remaining space is dedicated to record growth within the block. For a database with a 1K block size, the default toss limit is 150. For all other block sizes, the default toss limit is 300.

The create and toss limits are changed using PROUTIL. For Type I storage areas, create and toss limits are changed on a per area basis. For Type II storage areas, limits are changed on a per area or object basis. The general syntax for changing a create or toss limit with PROUTIL is as follows:

proutil db-name -C SET-obj-limittype-LIMIT object-idenitifer new-limit

Table 13–3 describes the PROUTIL qualifiers to available to change create and toss limits.

Table 13–3: PROUTIL qualifiers for changing create and toss limits

Limit	Object type	Qualifier
Create	Area	PROUTIL SETAREACREATELIMIT qualifier
	BLOB	PROUTIL SETBLOBCREATELIMIT qualifier
	Table	PROUTIL SETTABLECREATELIMIT qualifier
Toss	Area	PROUTIL SETAREATOSSLIMIT qualifier
	BLOB	PROUTIL SETBLOBTOSSLIMIT qualifier
	Table	PROUTIL SETTABLETOSSLIMIT qualifier

The current values for create and toss limits are displayed by using PROUTIL DISPTOSSCREATELIMITS. The limits are displayed for a specified area. See the "PROUTIL DISPTOSSCREATELIMITS qualifier" section for the complete syntax.

Create and toss limits should only be changed when your database experiences high rates of fragmentation or inefficient space utilization within blocks. Table 13–4 describes situations and suggested solutions.

Table 13–4: Create and toss limit situations and solutions

Situation	Suggested solution
Fragmentation occurs on updates to existing records. You anticipated 1 fragment, but 2 were created.	Increase create limit.
Fragmentation occurs on updates to existing records or you have many blocks on the RM chain with insufficient space to create new records.	Increase toss limit.
There is limited fragmentation, but database block space is being used inefficiently, and records are not expected to grow beyond their original size.	Decrease create limit.
There is limited fragmentation, but database block space is being used inefficiently, and records are not expected to grow beyond their original size.	Decrease toss limit.

Increasing the create and toss limits address record fragmentation by allowing more space for records to grow within a block before being continued in another block. Both limits identify an amount of space to reserve for existing records to grow within a block. If the create limit check fails, the new record will be inserted into a different block. It remains possible that a smaller record can be inserted into the first block because failing the create limit check does not remove it from the RM chain. If the toss limit check fails, when a record is expanded, the block is removed from the RM chain so no new records can be inserted into it.

Decreasing the create and toss limits address problems with efficient block space utilization. When blocks have large amounts of empty space and record growth is not anticipated, but new records are unable to be added because the limit checks are failing, you can consider decreasing the create and toss limits. Decreasing the create limit will increase the potential that a new record can be added to an existing record, and decreasing the toss limit will leave blocks with on the RM chain.